Custom Value Formatting

Custom Value Formatting provides users with the ability to change the numerical formatting applied to the results in the data grid. Data values are usually formatted by cube designers for each measure presented to the end user. Sometimes, the users want to tweak these formats by applying a custom value format.

The value format drop down menu on the grid ribbon provides the user with a variety of predefined format customizations. However, users can specify their own format masks.

Format Masks

The following mini-guide has been adapted from Microsoft Excel's value masking help.

Overview

To create a custom number format, you start by selecting one of the built-in number formats as a starting point. You can then change any one of the code sections of that format to create your own custom number format.

A number format can have up to four sections of code, separated by semicolons. These code sections define the format for positive numbers, negative numbers, zero values, and text, in that order.

<POSITIVE>;<NEGATIVE>;<ZERO>;

For example, you can use these code sections to create the following custom format:

#,##0.00;(#,##0.00);0.00;

You do not have to include all code sections in your custom number format. If you specify only two code sections for your custom number format, the first section is used for positive numbers and zeros, and the second section is used for negative numbers. If you specify only one code section, it is used for all numbers. If you want to skip a code section and include a code section that follows it, you must include the ending semicolon for the section that you skip.

USING DECIMAL PLACES, SPACES, COLORS, AND CONDITIONS

Include decimal places and significant digits To format fractions or numbers that contain decimal points, include the following digit placeholders, decimal points, and thousand separators in a section.

• 0 (zero) - This digit placeholder displays insignificant zeros if a number has fewer digits than there are zeros in the format. For example, if you type 8.9, and you want it to be displayed as 8.90, use the format #.00.
• # - This digit placeholder follows the same rules as the 0 (zero). However, Excel does not display extra zeros when the number that you type has fewer digits on either side of the decimal than there are # symbols in the format. For example, if the custom format is #.##, and you type 8.9 in the cell, the number 8.9 is displayed.
• ? - This digit placeholder follows the same rules as the 0 (zero). However, Excel adds a space for insignificant zeros on either side of the decimal point so that decimal points are aligned in the column. For example, the custom format 0.0? aligns the decimal points for the numbers 8.9 and 88.99 in a column.
• . (period) - This digit placeholder displays the decimal point in a number.

If a number has more digits to the right of the decimal point than there are placeholders in the format, the number rounds to as many decimal places as there are placeholders. If there are more digits to the left of the decimal point than there are placeholders, the extra digits are displayed. If the format contains only number signs (#) to the left of the decimal point, numbers less than 1 begin with a decimal point; for example, .47.

Display a thousands separator to display a comma as a thousands separator or to scale a number by a multiple of 1,000, include the following separator in the number format.